﻿CREATE VIEW [steve].[uvSteve_RevisionHistory]
	AS

select r.RevisionDate
	, r.RevisionMembershipId
	, r.RevisionComment
	, r.RevisionNumber
	, convert(varchar(50), 'SiteUrl') as [ItemType]
	, convert(nvarchar(100), h.Url) as [ItemDescription]
	, convert(varchar(50), h.SiteUrlId) as [ItemId]
from SiteUrlRevision r
	inner join SiteUrl h on h.SiteUrlId = r.SiteUrlId

union 

select r.RevisionDate
	, r.RevisionMembershipId
	, r.RevisionComment
	, r.RevisionNumber
	, convert(varchar(50), 'Content') as [ItemType]
	, convert(nvarchar(100), r.Title) as [ItemDescription]
	, convert(varchar(50), h.ContentId) as [ItemId]
from ContentRevision r
	inner join Content h on h.ContentId = r.ContentId

union 

select r.RevisionDate
	, r.RevisionMembershipId
	, r.RevisionComment
	, r.RevisionNumber
	, convert(varchar(50), 'UrlRewrite') as [ItemType]
	, convert(nvarchar(100), h.Url) as [ItemDescription]
	, convert(varchar(50), h.UrlRewriteId) as [ItemId]
from UrlRewriteRevision r
	inner join UrlRewrite h on h.UrlRewriteId = r.UrlRewriteId

union

select r.UpdateDate
	, r.UpdateMembershipId
	, null
	, null
	, CONVERT(varchar(50), 'Category') as [ItemType]
	, CONVERT(varchar(100), r.CategoryName) as [ItemDescription]
	, convert(varchar(50), r.CategoryName) as [ItemId]
from Category r

union 

select r.UpdateDate
	, r.UpdateMembershipId
	, null
	, null
	, CONVERT(varchar(50), 'Category') as [ItemType]
	, CONVERT(varchar(100), r.SiteName) as [ItemDescription]
	, convert(varchar(50), r.SiteName) as [ItemId]
from [Site] r

